﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using QLKN.DataAccess;

namespace QLKN
{
    public partial class Quanlysanpham : Form
    {
        SqlConnection conn;
        SqlCommand cmd, cmd1, cmd2, cmd3;
        SqlCommandBuilder cmb, cmb1;
        SqlDataAdapter da, da1, da2, da3;
        DataSet ds, ds1, ds2, ds3;
        DataTable dt, dt1, dt2, dt3;

        public Quanlysanpham()
        {
            InitializeComponent();
        }

        private void Quanlysanpham_Load(object sender, EventArgs e)
        {
            conn = Data.getConnection();
            cmd = new SqlCommand("SELECT a.sid, a.stensanpham, b.ltenloaisanpham, a.sgia, a.sthoigianbaohanh, a.smota, a.strangthai from Sanpham a, Loaisanpham b where a.lid = b.lid;", conn);
            //cmd = new SqlCommand("Select * from Sanpham", conn);
            conn.Open();
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "San Pham");
            cmb = new SqlCommandBuilder(da);
            dgvSP.DataSource = ds.Tables["San Pham"];

            //dataset update
            cmd1 = new SqlCommand("Select * from Sanpham", conn);
            da1 = new SqlDataAdapter(cmd1);
            ds1 = new DataSet();
            da1.Fill(ds1, "SP");
            cmb1 = new SqlCommandBuilder(da1);

            //comboxTK
            cmd2 = new SqlCommand("SELECT * FROM Loaisanpham", conn);
            da2 = new SqlDataAdapter(cmd2);
            ds2 = new DataSet();
            da2.Fill(ds2, "LoaiSP");
            dt2 = ds2.Tables["LoaiSP"];
            cbTimkiemloaiSP.DataSource = dt2;
            cbTimkiemloaiSP.DisplayMember = "ltenloaisanpham";
            cbTimkiemloaiSP.ValueMember = "ltenloaisanpham";

            //combox loaisp
            da3 = new SqlDataAdapter(cmd2);
            ds3 = new DataSet();
            da3.Fill(ds3, "LoaiSP");
            dt3 = ds3.Tables["LoaiSP"];
            cbLoaiSP.DataSource = dt3;
            cbLoaiSP.DisplayMember = "ltenloaisanpham";
            cbLoaiSP.ValueMember = "lid";
            conn.Close();

        }

        private void dgvSP_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            int dong = e.RowIndex;
            txtTenSP.Text = dgvSP.Rows[dong].Cells["stensanpham"].Value.ToString();
            txtGia.Text = dgvSP.Rows[dong].Cells["sgia"].Value.ToString();
            cbLoaiSP.Text = dgvSP.Rows[dong].Cells["lTenloaisanpham"].Value.ToString();
            txtTgBH.Text = dgvSP.Rows[dong].Cells["sthoigianbaohanh"].Value.ToString();
            txtMota.Text = dgvSP.Rows[dong].Cells["smota"].Value.ToString();
            Boolean test = bool.Parse(dgvSP.Rows[dong].Cells["strangthai"].Value.ToString());
            if (test == true) { ckCheck.Checked = true; }
        }

        private void Reload()
        {
            conn.Open();
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "San Pham");
            cmb = new SqlCommandBuilder(da);

            dgvSP.DataSource = ds.Tables["San Pham"];
        }


        private void btThem_Click(object sender, EventArgs e)
        {
            dt1 = ds1.Tables["SP"];
            DataRow newRow = dt1.NewRow();
            newRow["stensanpham"] = txtTenSP.Text;
            newRow["lid"] = cbLoaiSP.SelectedValue;
            newRow["sgia"] = txtGia.Text;
            newRow["sthoigianbaohanh"] = txtTgBH.Text;
            newRow["smota"] = txtMota.Text;
            if (ckCheck.Checked == true)
            {
                newRow["strangthai"] = "true";
            }
            else newRow["strangthai"] = "false";

            dt1.Rows.Add(newRow);
            da1.Update(ds1, "SP");
            MessageBox.Show("Thêm mới thành công");
            Reload();
        }

        private void btClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btRefresh_Click(object sender, EventArgs e)
        {
            txtTenSP.Text = "";
            txtGia.Text = "";
            txtTgBH.Text = "";
            txtMota.Text = "";
            cbLoaiSP.Text = "";
            ckCheck.Checked = false;
        }

        private void btSua_Click(object sender, EventArgs e)
        {
            dt1 = ds1.Tables["SP"];
            int dong = dgvSP.CurrentRow.Index;
            dt1.Rows[dong]["stensanpham"] = txtTenSP.Text;
            dt1.Rows[dong]["lid"] = cbLoaiSP.SelectedValue;
            dt1.Rows[dong]["sgia"] = txtGia.Text;
            dt1.Rows[dong]["sthoigianbaohanh"] = txtTgBH.Text;
            dt1.Rows[dong]["smota"] = txtMota.Text;
            if (ckCheck.Checked)
            {
                dt1.Rows[dong]["strangthai"] = true;
            }
            da1.Update(ds1, "SP");
            MessageBox.Show("Sửa thông tin thành công!");
            Reload();
        }

        private void btXoa_Click(object sender, EventArgs e)
        {
            int i = dgvSP.CurrentRow.Index;
            DialogResult ketqua = MessageBox.Show("Bạn có chắc muốn xóa bản ghi này không?", "Thông báo", MessageBoxButtons.YesNo);
            if (ketqua == DialogResult.Yes)
            {
                ds1.Tables["SP"].Rows[i].Delete();
                da1.Update(ds1, "SP");
                MessageBox.Show("Xóa thành công!");
                String count = ds1.Tables[0].DefaultView.Count.ToString();
                MessageBox.Show("Bạn có " + count + "" + "bản ghi.");
                Reload();
            }
        }

        private void btTimkiem_Click(object sender, EventArgs e)
        {
            String timkiem = cbTimkiem.SelectedItem.ToString();
            //MessageBox.Show(timkiem);
            switch (timkiem)
            {
                case "Mã sản phẩm":
                    int a = int.Parse(txtTimkiem.Text);
                    cmd = new SqlCommand("Select * From Sanpham Where sid like '%" + a + "%'", conn);
                    conn.Open();
                    da = new SqlDataAdapter(cmd);
                    ds = new DataSet();
                    da.Fill(ds, "San Pham");
                    dgvSP.DataSource = ds.Tables["San Pham"];
                    conn.Close();
                    break;
                case "Tên sản phẩm":
                    cmd = new SqlCommand("Select * From Sanpham Where stensanpham like N'%" + txtTimkiem.Text + "%'", conn);
                    conn.Open();
                    da = new SqlDataAdapter(cmd);
                    ds = new DataSet();
                    da.Fill(ds, "San Pham");
                    dgvSP.DataSource = ds.Tables["San Pham"];
                    conn.Close();
                    break;
                case "Mô tả":
                    cmd = new SqlCommand("Select * From Sanpham Where smota like N'%" + txtTimkiem.Text + "%'", conn);
                    conn.Open();
                    da = new SqlDataAdapter(cmd);
                    ds = new DataSet();
                    da.Fill(ds, "San Pham");
                    dgvSP.DataSource = ds.Tables["San Pham"];
                    conn.Close();
                    break;
            }
        }

        private void btOK_Click(object sender, EventArgs e)
        {
            //MessageBox.Show("" + cbTimkiemloaiSP.SelectedValue);
            //ds.Tables["San Pham"].DefaultView.RowFilter = "lTenloaisanpham LIKE'%" + cbTimkiemloaiSP.SelectedValue.ToString() + "%'";
            ds.Tables["San Pham"].DefaultView.RowFilter = "lTenloaisanpham='" + cbTimkiemloaiSP.SelectedValue.ToString() + "'";
            //ds.Tables["San Pham"].DefaultView.RowFilter = "sTensanpham=Nokia 110i";
        }
    }
}
